| 2、Linux 坏境MySQL数据目录和字符集修改 | 您所在的位置：网站首页 › mysql error1366 › 2、Linux 坏境MySQL数据目录和字符集修改 |
2、Linux 坏境MySQL数据目录和字符集修改
|
由于我服务器上的磁盘分配/data为数据盘，空间比较大,所以要修改一下mysql的数据目录 1、查询MySQL数据目录使用show variables命令查询数据目录 mysql> show variables like '%datadir%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.00 sec)可以看出我们的数据目录在/var/lib/mysql/目录下 2、创建MySQL的数据目录 [root@test1 ~]# mkdir -p /data/mysql/data 3、关闭MySQL服务缺认是否关闭MySQL服务 [root@test1 ~]# systemctl stop mysqld [root@test1 ~]# systemctl status mysqld 4、复制数据文件复制数据文件到我们新创建的数据位置，并将原有的目录改名，确保数据库目录修改成功后在删除 [root@test1 ~]# cp -R /var/lib/mysql/* /data/mysql/data/ [root@test1 ~]# mv /var/lib/mysql /var/lib/mysqlback 5、修改数据目录的属主和属组 [root@test1 ~]# chown -R mysql:mysql /data/mysql/ 6、修改配置文件修改配置文件datadir和socket的值 [root@test1 ~]# vi /etc/my.cnf [root@test1 data]# cat /etc/my.cnf [mysqld] datadir=/data/mysql/data socket=/data/mysql/data/mysql.scok symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
7、重启MySQL登陆数据库查看
[root@test1 data]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
数据库服务启动的时候报错,查看日志，大致如下 [root@test1 data]# cat /var/log/mysqld.log 2023-03-12T05:47:21.956736Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation. 2023-03-12T05:47:21.956767Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory. 2023-03-12T05:47:21.956781Z 0 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions 2023-03-12T05:47:21.956789Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error这是因为selinux开启导致的，我们关闭selinux和防火墙 #关闭防火墙 [root@test1 data]# systemctl stop firewalld.service #开机不启动防火墙 [root@test1 data]# systemctl disable firewalld.service #查询防火墙状态 [root@test1 data]# systemctl status firewalld.service #永久关闭SELINUX [root@test1 data]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config #临时关闭SELINUX [root@test1 data]# setenforce 0 #查看SELINUX状态 [root@test1 data]# getenforce重新启动MySQL，一切OK [root@test1 data]# systemctl start mysqld [root@test1 data]# systemctl status mysqld #查询MySQL端口 [root@test1 data]# ss -tnl | grep 3306 LISTEN 0 80 [::]:3306 [::]:*登陆MySQL，发现报错 [root@test1 data]# mysql -uroot -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)解决方法： 方法1:既然找不到/var/lib/mysql/mysql.sock,那我直接做个软连接将/data/mysql/data/mysql.sock直接连接过去就可以了，实测实可以用的。 方法2:修改my.cnf配置文件 [mysql] socket=/home/mysql/data/mysql.sock [client] socket=/data/mysql/data/mysql.scok
测试，MySQL可以正常登陆 [root@test1 data]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.39 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 8、修改MySQL字符集我们可以使用show variables like '%char%'命令查询MySQL的字符集 mysql> show variables like '%char%'; +--------------------------------------+----------------------------+ | Variable_name | Value | +--------------------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | validate_password_special_char_count | 1 | +--------------------------------------+----------------------------+ 9 rows in set (0.00 sec)生产过程中，大多数系统默认字符集是utf8，所以我们需要对MySQL字符集进行修改，我们修改my.cnf配置文件 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8 collation-server = utf8_unicode_ci init_connect='SET NAMES utf8' [client] default-character-set=utf8 [mysql] default-character-set=utf8修改完成后配置文件如下 [root@test1 data]# cat /etc/my.cnf [mysqld] datadir=/data/mysql/data socket=/data/mysql/data/mysql.scok symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid character-set-client-handshake = FALSE character-set-server = utf8 collation-server = utf8_unicode_ci init_connect='SET NAMES utf8' [client] socket=/data/mysql/data/mysql.scok default-character-set=utf8 [mysql] socket=/data/mysql/data/mysql.scok default-character-set=utf8我们现在查询MySQL字符集 mysql> show variables like '%char%'; +--------------------------------------+----------------------------+ | Variable_name | Value | +--------------------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | validate_password_special_char_count | 1 | +--------------------------------------+----------------------------+ 9 rows in set (0.00 sec) 9、忽略大小写生产过程中，有时候数据库的表名为大写，而我们使用小写导致报错，我们修改my.cnf配置文件 [mysqld] lower_case_table_names = 1修改完成后配置文件如下 [root@test1 data]# cat /etc/my.cnf [mysqld] datadir=/data/mysql/data socket=/data/mysql/data/mysql.scok symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid character-set-client-handshake = FALSE character-set-server = utf8 collation-server = utf8_unicode_ci init_connect='SET NAMES utf8' [client] socket=/data/mysql/data/mysql.scok default-character-set=utf8 lower_case_table_names = 1 [mysql] socket=/data/mysql/data/mysql.scok default-character-set=utf8数据库目前迁移、字符集修改和忽略大小写就此结束，有什么疑问可以评论留言！！！ |
【本文地址】
公司简介
联系我们